<?php
$nns_db_service_dir = dirname(dirname(__FILE__));
require_once $nns_db_service_dir . DIRECTORY_SEPARATOR . "nns_common" . DIRECTORY_SEPARATOR . "nns_db_class.php";
require_once $nns_db_service_dir . DIRECTORY_SEPARATOR . "nns_common" . DIRECTORY_SEPARATOR . "nns_db_guid_class.php";
require_once $nns_db_service_dir . DIRECTORY_SEPARATOR . "nns_vod" . DIRECTORY_SEPARATOR . "nns_db_vod_class.php";
require_once $nns_db_service_dir . DIRECTORY_SEPARATOR . "nns_live" . DIRECTORY_SEPARATOR . "nns_db_live_class.php";
class nns_db_assist_item_class {
	private $conn;
	private $db_obj;

	public function __construct() {
		$this->db_obj = new nns_db_class(g_db_host, g_db_username, g_db_password, g_db_name);
		$this->conn = $this->db_obj->nns_db_connect();
	}
	public function nns_db_assist_item_count($assist_id = null, $assist_detail_id = null, $video_id = null,$video_type = null, $check=1,$video_name=null,$asset_like_category_id=null,$nns_pinyin=null,$nns_tag=null,$nns_en_index=null,$nns_name_first_char=null) {
		$str_sql = "select count(1) as num from nns_assists_item ";
		$str_sql .= $this->nns_db_assist_item_where(0, 0, $assist_id, $assist_detail_id, $video_id, $video_type,$check,$video_name, null,$asset_like_category_id,$nns_pinyin,$nns_tag,$nns_en_index,$nns_name_first_char);
//		echo $str_sql;die;
		//result
		$result = array ();
		$result = $this->db_obj->nns_db_query($str_sql);

		return $result;
	}
	//2012-8-30 bo.chen@starcorcn.com 用于统计媒资包分类影片数据
	public function nns_db_assist_category_count($sql){
		$result = array ();
		$result = $this->db_obj->nns_db_query($sql);

		return $result;
	}

	public function nns_db_assist_item_list($min = null, $num = null, $assist_id = null, $assist_detail_id = null, $video_id = null, $video_type = null,$check=1,$video_name=null, $order = null,$asset_like_category_id=null,$nns_pinyin=null,$nns_tag=null,$nns_en_index=null,$nns_name_first_char=null) {
		$str_sql = " select * from nns_assists_item ";
		$str_sql .= $this->nns_db_assist_item_where($min, $num, $assist_id, $assist_detail_id, $video_id, $video_type, $check,$video_name,$order,$asset_like_category_id,$nns_pinyin,$nns_tag,$nns_en_index,$nns_name_first_char);
//		echo $order.'|';
//		echo $str_sql;die;
		//result
		$result = array ();
		$result = $this->db_obj->nns_db_query($str_sql);
		if ($result["ret"] == 0) {
			if ($this->db_obj->num_rows <= 0) {
				$result = $this->db_obj->nns_db_result(NNS_ERROR_USER_NO_DATA, 'service data not find');
			}
		}
		return $result;
	}
	 public function nns_db_assist_item_info($id, $video = '') {
                $result = array();
                if (!empty($video)) {
                        $str_sql = "select * from nns_assists_item where nns_video_id='$video'";
                } else if (empty($id)) {
                        $result = $this->db_obj->nns_db_result(NNS_ERROR_ARGS, 'service info param error');
                        return $result;
                } else {
                        $str_sql = "select * from nns_assists_item where nns_id='$id'";
                }
                $result = $this->db_obj->nns_db_query($str_sql);

                if ($result["ret"] == 0) {
                        if ($this->db_obj->num_rows <= 0) {
                                $result = $this->db_obj->nns_db_result(NNS_ERROR_DEVICE_NOT_FOUND, 'service info not find');
                        }
                }

                return $result;
        }
	public function nns_db_assist_item_add($video_id = null, $video_type = null, $video_name = null, $video_image1 = null,
	 $video_image2 = null, $video_image3 = null, $video_image4 = null, $video_image5 = null, $video_image6 = null,
	 $nns_point=null ,$assist_id = null, $assist_detail_id = null,$check=1,$play_count=0,$play_day_count=0,$score=0,
	 $score_count=0,$nns_pinyin=null,$nns_tag=null,$nns_english_index=null) {
		$result = array ();

		$video_type = (int) $video_type;
		$nns_point = (int) $nns_point;
		//生成GUID
		$guid = new Guid();
		$id = $guid->toString();
		$num_result=$this->nns_db_assist_item_count($assist_id,$assist_detail_id);
		$num=$num_result["data"][0]["num"];
		if ($num<0) $num=0;
		//insert
		$str_sql = "insert into nns_assists_item(nns_id,nns_video_id,nns_video_type,nns_video_name,nns_video_img0," .
				"nns_video_img1,nns_video_img2,nns_video_img3,nns_video_img4,nns_video_img5,nns_point,nns_category_id," .
				"nns_assist_id,nns_check,nns_play_count,nns_play_day_count,nns_score,nns_score_count,nns_order,nns_pinyin," .
				"nns_tag,nns_english_index" .
				")values(" .
		"'$id','$video_id',$video_type,'$video_name','$video_image1','$video_image2','$video_image3','$video_image4'," .
		"'$video_image5','$video_image6',$nns_point,'$assist_id','$assist_detail_id',$check,$play_count,$play_day_count," .
		"$score,$score_count,$num,'$nns_pinyin','$nns_tag','$nns_english_index')";
		$result = $this->db_obj->nns_db_execute($str_sql);
		return $result;

	}

	public function nns_db_assist_item_add_more($nns_ids, $video_type, $assist_id, $assist_detail_id) {
		$result=array();
		$result["ret"]=1;
		$nns_video_arr = explode(",", $nns_ids);

		if ($video_type==0){
		$video_inst=new nns_db_vod_class();
		}else if ($video_type==1){
		$video_inst=new nns_db_live_class();
		}
		foreach ($nns_video_arr as $video_id) {
			if ($video_id!=""){
			$result = $this->nns_db_assist_item_count($assist_id, $assist_detail_id, $video_id, $video_type);
			$exist_video_arr=$this->nns_db_assist_item_list(0,1,$assist_id,$assist_detail_id,$video_id);
				if ($result["ret"] == 0) {
					if ($result["data"][0]["num"] == 0) {

						if ($video_type==0){
							$result=$video_inst->nns_db_vod_info($video_id,"nns_id,nns_name,nns_image0,nns_image1,nns_image2,nns_image3,nns_image4,nns_image5,nns_point,nns_pinyin,nns_tag,nns_eng_name,nns_play_count");
						}else if($video_type==1){
							$result=$video_inst->nns_db_live_info($video_id,"nns_id,nns_name,nns_image0,nns_image1,nns_image2,nns_image3,nns_image4,nns_image5,nns_point,nns_pinyin,nns_tag,nns_eng_name,nns_play_count");
						}
						if ($result["ret"]==0){
							if ($exist_video_arr["ret"]==0){
								$exist_video=$exist_video_arr["data"][0];
								$play_count=$exist_video["nns_play_count"];
								$play_day_count=$exist_video["nns_play_day_count"];
								$score=$exist_video["nns_score"];
								$score_count=$exist_video["nns_score_count"];
							}
							$play_count=(int)$play_count;
							$play_day_count=(int)$play_day_count;
							$score=(int)$score;
							$score_count=(int)$score_count;
							$data=$result["data"][0];
							$result=$this->nns_db_assist_item_add($video_id,$video_type,$data["nns_name"],$data["nns_image0"],
							$data["nns_image1"],$data["nns_image2"],$data["nns_image3"],$data["nns_image4"],$data["nns_image5"]
							,$data["nns_point"],$assist_id,$assist_detail_id,1,$play_count,$play_day_count,$score,$score_count,
							$data["nns_pinyin"],$data['nns_tag'],$data['nns_eng_name']);
						}
					}
				}
			}
		}
		return $result;
	}

	public function nns_db_assist_item_order_exchange($item_id,$order,$dir,$assist_id,$assist_detail_id){
		$result=array();
		$result["ret"]=0;
		if ($dir=="pre"){
			$num_result=$this->nns_db_assist_item_count($assist_id,$assist_detail_id);
			$num=$num_result["data"][0]["num"]-1;
			$order1=$order+1;
			if ($order1>$num){
				$order1=$order;
				 return $result;
			}
		}else if ($dir=="next"){
			$order1=$order-1;
			if ($order1<0){
				$order1=0;
				 return $result;
			}
		}
		$str_sql = "update nns_assists_item set nns_order=$order where nns_order=$order1 and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
			$result = $this->db_obj->nns_db_execute($str_sql);
			$str_sql = "update nns_assists_item set nns_order=$order1 where nns_id='$item_id' and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
			$result = $this->db_obj->nns_db_execute($str_sql);
		return $result;
	}

	public function nns_db_assist_item_order_up($item_id1,$old_order,$assist_id,$assist_detail_id){
		$result=$this->nns_db_assist_item_count($assist_id,$assist_detail_id);
		$num=$result["data"][0]["num"]-1;
		$str_sql = "update nns_assists_item set nns_order=nns_order-1 where nns_order>$old_order and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
		$result = $this->db_obj->nns_db_execute($str_sql);
		$str_sql = "update nns_assists_item set nns_order=$num where nns_id='$item_id1' and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
		$result = $this->db_obj->nns_db_execute($str_sql);
		return $result;
	}

	public function nns_db_assist_item_order_down($item_id1,$old_order,$assist_id,$assist_detail_id){
		$result=$this->nns_db_assist_item_count($assist_id,$assist_detail_id);
		$num=0;
		$str_sql = "update nns_assists_item set nns_order=nns_order+1 where nns_order<$old_order  and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
		$result = $this->db_obj->nns_db_execute($str_sql);
		$str_sql = "update nns_assists_item set nns_order=$num where nns_id='$item_id1'  and nns_category_id='$assist_id' and nns_assist_id='$assist_detail_id'";
		$result = $this->db_obj->nns_db_execute($str_sql);
		return $result;
	}

	public function nns_db_assist_item_move_category($id,  $asset_id = null, $categroy_id = null) {
		$result = array ();
		if (!isset ($id)) {
			$result = $this->db_obj->nns_db_result(NNS_ERROR_ARGS, 'modify service param error');
			return $result;
		}
		if (empty ($id)) {
			$result = $this->db_obj->nns_db_result(NNS_ERROR_ARGS, 'modify service param error');
			return $result;
		}

		//exist
		$str_sql = "select * from nns_assists_item where nns_id='$id'";
		$result = $this->db_obj->nns_db_query($str_sql);
		if ($result["ret"] != 0) {
			return $result;
		}
		
		
		if (count($result["data"])> 0) {
			$old_data=$result["data"][0];
			$str_sql="select count(1) as num from nns_assists_item where nns_video_id='".$old_data["nns_video_id"]."' and nns_assist_id='$asset_id' and nns_category_id='$categroy_id'";
			$result = $this->db_obj->nns_db_query($str_sql);
			if ($result["data"][0]["num"]==0){
				//先将要转移的ITEM移到order 0 位置
				$this->nns_db_assist_item_order_down($id,$old_data["nns_order"],$old_data["nns_category_id"],$old_data["nns_assist_id"]);
//				计算转移栏目的ORDER值
				$str_sql="select count(1) as num from nns_assists_item where nns_assist_id='$asset_id' and nns_category_id='$categroy_id'";
				$result = $this->db_obj->nns_db_query($str_sql);
				//update
				$str_sql = "update nns_assists_item set nns_assist_id='$asset_id',nns_category_id='$categroy_id',nns_order='".$result["data"][0]["num"]."' where nns_id='$id'";
				//			echo $str_sql;
				$result = $this->db_obj->nns_db_execute($str_sql);
			}
		}else{
			$result['ret']=2;
		}
		return $result;
	}

	public function nns_db_assist_item_delete($id) {
		$result = array ();
		if (!isset ($id)) {
			$result = $this->db_obj->nns_db_result(NNS_ERROR_ARGS, 'delete service param error');
			return $result;
		}
		if (empty ($id)) {
			$result = $this->db_obj->nns_db_result(NNS_ERROR_ARGS, 'delete service param error');
			return $result;
		}
		$result=$this->nns_db_assist_item_info($id);
		$user_info=$result["data"][0];
		$this->nns_db_assist_item_order_up($user_info["nns_id"],$user_info["nns_order"],$user_info["nns_category_id"],$user_info["nns_assist_id"]);
		$str_sql = "delete from nns_assists_item where nns_id='$id'";
		$result = $this->db_obj->nns_db_execute($str_sql);


		return $result;

	}

	public function nns_db_assist_item_where($min = null, $num = null, $assist_id = null, $assist_detail_id = null,
	$video_id = null, $video_type = null,$check=null,$video_name=null, $order = null,$asset_like_category_id=null,
	$nns_pinyin=null,$nns_tag=null,$nns_en_index=null,$nns_name_first_char=null) {
		//组合sql
		$where = "";

		if (isset ($assist_id)) {
			if (!empty ($assist_id)) {
				$assist_id_arr=explode("|",$assist_id);
				$item_num=0;
				foreach ($assist_id_arr as $assist_id_item){
					if (empty ($where)) {
						$where .= " where ( nns_category_id = '$assist_id_item' ";
					} else {
						if ($item_num==0){
							$where .= " and ( nns_category_id='$assist_id_item'";
						}else{
							$where .= " or nns_category_id='$assist_id_item'";
						}
					}
					$item_num++;
				}
				$where .=' ) ';
			}
		}

		if (isset ($nns_pinyin)) {
			if (!empty ($nns_pinyin)) {
				if (empty ($where)) {
					$where .= " where nns_pinyin like '$nns_pinyin%' ";
				} else {
					$where .= " and nns_pinyin like '$nns_pinyin%'";
				}
			}
		}
		if (isset ($nns_en_index)) {
			if (!empty ($nns_en_index)) {
				if (empty ($where)) {
					$where .= " where nns_english_index like '$nns_en_index%' ";
				} else {
					$where .= " and nns_english_index like '$nns_en_index%'";
				}
			}
		}
		
		if (isset ($nns_tag)) {
				if (empty ($where)) {
					$where .= " where " .
							" ( ISNULL(nns_tag) or nns_tag='' or LOCATE(',$nns_tag,',CONCAT(',',nns_tag))>0 ) and " .
							" ( ISNULL(nns_single_tag) or nns_single_tag='' or LOCATE(',$nns_tag,',CONCAT(',',nns_single_tag))>0 ) ";
				} else {
					$where .= " and  " .
							" ( ISNULL(nns_tag) or nns_tag='' or LOCATE(',$nns_tag,',CONCAT(',',nns_tag))>0 ) and " .
							" ( ISNULL(nns_single_tag) or nns_single_tag='' or LOCATE(',$nns_tag,',CONCAT(',',nns_single_tag))>0 ) ";
				}
		}
		

		if (isset ($asset_like_category_id)) {
			if (!empty ($asset_like_category_id)) {
				if (empty ($where)) {
					$where .= " where nns_category_id like '$asset_like_category_id%' ";
				} else {
					$where .= " and nns_category_id like '$asset_like_category_id%'";
				}
			}
		}



		if (isset ($assist_detail_id)) {
			if (!empty ($assist_detail_id)) {
				$assist_id_arr=explode("|",$assist_detail_id);
				$item_num=0;
				foreach ($assist_id_arr as $assist_id_item){
					if (empty ($where)) {
						$where .= " where ( nns_assist_id='$assist_id_item'";
					} else {
						if ($item_num==0){
							$where .= " and ( nns_assist_id='$assist_id_item'";
						}else{
							$where .= " or nns_assist_id='$assist_id_item'";
						}
					}
					$item_num++;
				}
				$where .=' ) ';
			}
		}

		if (isset ($video_id)) {
			if (!empty ($video_id)) {
				if (empty ($where)) {
					$where .= " where nns_video_id='$video_id'";
				} else {
					$where .= " and nns_video_id='$video_id'";
				}
			}
		}

		if (isset ($video_type)) {
			if (!empty ($video_type)) {
				$video_id = (int) $video_id;
				if (empty ($where)) {
					$where .= " where nns_video_type=$video_type";
				} else {
					$where .= " and nns_video_type=$video_type";
				}
			}
		}

		if (isset ($check)) {
			if (!empty ($check)) {
				$check = (int) $check;
				if (empty ($where)) {
					$where .= " where nns_check=$check";
				} else {
					$where .= " and nns_check=$check";
				}
			}
		}

		if (! empty ( $video_name )) {
			
			if (is_array($video_name)){
				$query=' (';
				foreach ($video_name as $k=>$v){
					$query.= " {$k} like '%{$v}%' or";
				}
				
				$query=rtrim($query,'or');
				$query.=')';
			}else{
				$query=" nns_video_name like '%$video_name%' ";
			}
			
			if (empty ( $where )) {
				$where .= " where {$query}";
			} else {
				$where .= " and {$query}";
			}
		}
		
		if (isset ($nns_name_first_char)) {
			if (!empty ($nns_name_first_char)) {
				if (empty ($where)) {
					$where .= " where nns_video_name LIKE '$nns_name_first_char%'    ";
				} else {
					$where .= " and nns_video_name LIKE '$nns_name_first_char%' ";
				}
			}
		}


		if (isset ($order)) {
			if (!empty ($order)) {
				$where .= " order by $order ";
			}else{
				$where .= " order by nns_order desc";
			}
		}else{
				$where .= " order by nns_order desc";
		}
		if (isset ($min) && isset ($num)) {
			if (!empty ($num)) {
				$min = ( int ) $min;
				$num = ( int ) $num;
				$where .= " limit $min,$num";
			}
		}
		return $where;
	}

//下线影片
public function nns_db_assist_item_unline($video_id){
		$str_sql = "select nns_id from nns_assists_item where nns_video_id='$video_id'";
		$result = $this->db_obj->nns_db_query ( $str_sql );
		
		if (count($result['data'])>0){
			foreach ($result['data'] as $item){
				$result=$this->nns_db_assist_item_delete($item['nns_id']);
			}
		}
		return $result;
	}


//	前端调用统计接口
//	媒资包被点击次数
	public function nns_db_assist_item_play_count_add($video_id){
		$str_sql = "update nns_assists_item set nns_play_count=IFNULL(nns_play_count,0)+1,nns_play_day_count=IFNULL(nns_play_day_count,0)+1 where nns_video_id='$video_id'";
		$result = $this->db_obj->nns_db_execute($str_sql);

		return $result;
	}

	public function nns_db_assist_item_score_count_add($video_id){
		$str_sql = "update nns_assists_item set nns_score_count=IFNULL(nns_score_count,0)+1 where nns_video_id='$video_id'";
		$result = $this->db_obj->nns_db_query($str_sql);

		return $result;
	}
	public function nns_db_assist_item_score_add($socre,$video_id){
		$str_sql = "update nns_assists_item set nns_score=IFNULL(nns_score,0)+$socre where nns_video_id='$video_id'";
		$result = $this->db_obj->nns_db_query($str_sql);

		return $result;
	}

	public function nns_db_clear_play_day_count(){
		$str_sql = "update nns_assists_item set nns_play_day_count=0";
		$result = $this->db_obj->nns_db_execute($str_sql);
		return $result;
	}

	public function nns_db_total_play_top100_list($asset_id,$asset_item_id=null){
		$asset_item_id_str="";
		if ($asset_item_id){
			$asset_item_id_str=" and nns_category_id = '$asset_item_id' ";
		}
		$str_sql = "select * from nns_assists_item where nns_assist_id='$asset_id' ".$asset_item_id_str." group by nns_video_id order by nns_play_count desc limit 0,100";
		$result = $this->db_obj->nns_db_query($str_sql);
		return $result;
	}

	public function nns_db_day_play_top100_list($asset_id,$asset_item_id=null){
		$asset_item_id_str="";
		if ($asset_item_id){
			$asset_item_id_str=" and nns_category_id = '$asset_item_id' ";
		}
		$str_sql = "select * from nns_assists_item  where nns_assist_id='$asset_id' ".$asset_item_id_str." group by nns_video_id order by nns_play_day_count desc limit 0,100";
		$result = $this->db_obj->nns_db_query($str_sql);
		return $result;
	}
	
//	调整媒资的输出EPG标识
	public function nns_db_epg_control($asset_item_id,$tags){
		$str_sql = "update nns_assists_item set nns_single_tag='".$tags."' where nns_id='".$asset_item_id."'";
		$result = $this->db_obj->nns_db_execute($str_sql);
//		$result['ret']=$str_sql;
		return $result;
	}
}
?>